﻿using System;
using System.Collections.Generic;
using System.Text;
using System.Data.SqlClient;

namespace POSPlus.EZBarTender.DatabaseOperations
{
	public class Sign : DatabaseObject
	{

		#region Properties
		public String Upc { get { return _upc; }
			set
			{
				_upc = value;
				
				while (_upc.Length < 13)
				{
					_upc = "0" + _upc;
				}
			}
		}
		public String Desc1
		{
			get{return _desc1;}
			set
			{
				if (Convert.IsDBNull(value) || value == null)
				{
					_desc1 = "";
				}
				else
				{
					_desc1 = value;
				}
			}
		}
		public String Desc2 { get; set; }
		public String Desc3 { get; set; }
		public String Desc4 { get; set; }
		public String Desc5 { get; set; }
		public double NormalPrice { get; set; }
		public double PriceUom { get; set; }
		public String Selling { get; set; }
		public String Size { 
			get
			{
				return _size;
			}
			set
			{
				if (value.Split(' ').Length > 1)
				{
					_size = value.Split(' ')[0];
				}
				else
					_size = value;
			}
		}
		public String Uom { get; set; }
		public DateTime WeekEnding { get; set; }
		public double YouSave { get; set; }
        public bool IsNew { get; set; } 
        public DateTime StartDate { get; set; }
		#endregion

		#region Members
		private string _upc;
		private String _desc1;
		private String _size;
		private string LocalConnectionString;		
		#endregion

		#region Constructor
		public Sign(String connectionString)
		{
			ConnectionString = connectionString;
			PopulateDefault();
		}

		public Sign(String connectionString, String upc, DateTime weekEnding)
		{
			ConnectionString = connectionString;
			using (SqlConnection connection = new SqlConnection(ConnectionString))
			{
				connection.Open();
				SqlCommand command = new SqlCommand("SELECT Upc, Desc1, Desc2, Desc3, Desc4 " +
					"Desc5, NormalPrice, PriceUom, Selling, Size, Uom, WeekEnding, YouSave, IsNew " +
					"FROM POSplus_SignsPrinting WHERE UPC = @UPC AND WeekEnding = @WeekEnding");
				command.Parameters.AddWithValue("@UPC", upc);
				command.Parameters.AddWithValue("@WeekEnding", weekEnding);
				using (SqlDataReader reader = command.ExecuteReader())
				{
					if (reader.Read())
					{
						Upc = reader["Upc"].ToString();
						Desc1 = reader["Desc1"].ToString();
						Desc2 = reader["Desc2"].ToString();
						Desc3 = DatabaseObject.FromDB<String>(reader["Desc3"]);
						Desc4 = reader["Desc4"].ToString();
						Desc5 = reader["Desc5"].ToString();
						NormalPrice = Convert.ToDouble(reader["NormalPrice"]);
						PriceUom = Convert.ToDouble(reader["PriceUom"]);
						Selling = Convert.ToString(reader["Selling"]);
						Size = Convert.ToString(reader["Size"]);
						Uom = Convert.ToString(reader["Uom"]);
						WeekEnding = Convert.ToDateTime(reader["WeekEnding"]);
						YouSave = Convert.ToDouble(reader["YouSave"]);
						IsNew = Convert.ToBoolean(reader["IsNew"]);
					}
					else
						PopulateDefault();
					reader.Close();
					connection.Close();
				}
			}
		}

		public Sign(string connectionString, string upc)
		{
			ConnectionString = connectionString;
			using (SqlConnection connection = new SqlConnection(ConnectionString))
			{
				connection.Open();
				SqlCommand command = new SqlCommand("SELECT Upc, Desc1, Desc2, Desc3, Desc4, " +
					"Desc5, NormalPrice, PriceUom, Selling, Size, Uom, WeekEnding, YouSave, IsNew " +
					"FROM POSplus_SignsPrinting WHERE UPC = @UPC",connection);
				command.Parameters.AddWithValue("@UPC", upc);
				using (SqlDataReader reader = command.ExecuteReader())
				{
					if (reader.Read())
					{
						Upc = reader["Upc"].ToString();
						Desc1 = reader["Desc1"].ToString();
						Desc2 = reader["Desc2"].ToString();
						Desc3 = DatabaseObject.FromDB<String>(reader["Desc3"]);
						Desc4 = reader["Desc4"].ToString();
						Desc5 = reader["Desc5"].ToString();
						NormalPrice = Convert.ToDouble(reader["NormalPrice"]);
						PriceUom = Convert.ToDouble(reader["PriceUom"]);
						Selling = Convert.ToString(reader["Selling"]);
						Size = Convert.ToString(reader["Size"]);
						Uom = Convert.ToString(reader["Uom"]);
						WeekEnding = Convert.ToDateTime(reader["WeekEnding"]);
						YouSave = Convert.ToDouble(reader["YouSave"]);
						IsNew = Convert.ToBoolean(reader["IsNew"]);
					}
					else
						PopulateDefault();
					reader.Close();
					connection.Close();
				}
			}
		}
		#endregion

		#region Methods
		/// <summary>
		/// Converts doubles to Strings using the following logic:
		///	
		/// If the amount is less than 0 return ""
		/// Otherwise if the amount is less than 1.00 convert to:
		///		First two decimals (no decimal point) followed by cent sign
		/// Otherwise if the amount is greater than or equal to 1.00 convert to:
		///		Round to two decimals padding the right side to two decimal places
		///		and one to the left
		///	
		/// </summary>
		/// <param name="amount"></param>
		/// <returns></returns>
		private String FormatMoney(double amount)
		{
			StringBuilder builder = new StringBuilder();

			if (amount < 1)
			{
				builder.Append((Math.Round(amount, 2) * 100).ToString("##\u00A2"));
			}
			else
			{
				builder.Append((Math.Round(amount, 2).ToString("0.00")));
			}

			return builder.ToString();
		}

		protected override void PopulateDefault()
		{
			Upc = "";
			Desc1 = "";
			Desc2 = "";
			Desc3 = "";
			Desc4 = "";
			Desc5 = "";
			NormalPrice = 0;
			PriceUom = 0;
			Selling = "";
			Size = "";
			Uom = "";
			WeekEnding = new DateTime(1900, 1, 1);
			YouSave = 0;
			IsNew = true;
		}

		public override Boolean Create()
		{
			int rowsAffected = 0;
			using (SqlConnection connection = new SqlConnection(ConnectionString))
			{
				connection.Open();
				using (SqlCommand command = new SqlCommand("INSERT INTO POSplus_SignsPrinting " +
					"(Upc, Desc1, Desc2, Desc3, Desc4, Desc5, NormalPrice, PriceUom, Selling, Size, Uom, WeekEnding, YouSave, IsNew) VALUES " +
					"(@Upc, @Desc1, @Desc2, @Desc3, @Desc4, @Desc5, @NormalPrice, @PriceUom, @Selling, @Size, @Uom, @WeekEnding, @YouSave, @IsNew)",
					connection))
				{
					command.Parameters.AddWithValue("@Upc", DatabaseObject.ToDB(Upc));
					command.Parameters.AddWithValue("@Desc1", DatabaseObject.ToDB(Desc1));
					command.Parameters.AddWithValue("@Desc2", DatabaseObject.ToDB(Desc2));
					command.Parameters.AddWithValue("@Desc3", DatabaseObject.ToDB(Desc3));
					command.Parameters.AddWithValue("@Desc4", DatabaseObject.ToDB(Desc4));
					command.Parameters.AddWithValue("@Desc5", DatabaseObject.ToDB(Desc5));
					command.Parameters.AddWithValue("@NormalPrice", NormalPrice);
					command.Parameters.AddWithValue("@PriceUom", PriceUom);
					command.Parameters.AddWithValue("@Selling", Selling);
					command.Parameters.AddWithValue("@Size", Size);
					command.Parameters.AddWithValue("@Uom", Uom);
					command.Parameters.AddWithValue("@WeekEnding", WeekEnding);
					command.Parameters.AddWithValue("@YouSave", YouSave);
					command.Parameters.AddWithValue("@IsNew", IsNew);
					rowsAffected = command.ExecuteNonQuery();
					connection.Close();
				}
			}
			return rowsAffected > 0;
		}

		public override Boolean Update()
		{
			if (Upc == "") return false;

			int rowsAffected = 0;

			using (SqlConnection connection = new SqlConnection(ConnectionString))
			{
				connection.Open();
				using (SqlCommand command = new SqlCommand("UPDATE POSplus_SignsPrinting SET " +
					"Upc = @Upc, Desc1 = @Desc1, Desc2 = @Desc2, Desc3 = @Desc3, Desc4 = @Desc4, Desc5 = @Desc5, " +
					"NormalPrice = @NormalPrice, PriceUom = @PriceUom, Selling = @Selling, Size = @Size, " +
					"Uom = @Uom, WeekEnding = @WeekEnding, YouSave = @YouSave, IsNew = @IsNew WHERE " +
					"Upc = @Upc", connection))
				{

					command.Parameters.AddWithValue("@Upc", DatabaseObject.ToDB(Upc));
					command.Parameters.AddWithValue("@Desc1", DatabaseObject.ToDB(Desc1));
					command.Parameters.AddWithValue("@Desc2", DatabaseObject.ToDB(Desc2));
					command.Parameters.AddWithValue("@Desc3", DatabaseObject.ToDB(Desc3));
					command.Parameters.AddWithValue("@Desc4", DatabaseObject.ToDB(Desc4));
					command.Parameters.AddWithValue("@Desc5", DatabaseObject.ToDB(Desc5));
					command.Parameters.AddWithValue("@NormalPrice", DatabaseObject.ToDB(NormalPrice));
					command.Parameters.AddWithValue("@PriceUom", DatabaseObject.ToDB(PriceUom));
					command.Parameters.AddWithValue("@Selling", DatabaseObject.ToDB(Selling));
					command.Parameters.AddWithValue("@Size", DatabaseObject.ToDB(Size));
					command.Parameters.AddWithValue("@Uom", DatabaseObject.ToDB(Uom));
					command.Parameters.AddWithValue("@WeekEnding", DatabaseObject.ToDB(WeekEnding));
					command.Parameters.AddWithValue("@YouSave", DatabaseObject.ToDB(YouSave));
					command.Parameters.AddWithValue("@IsNew", DatabaseObject.ToDB(IsNew));
					rowsAffected = command.ExecuteNonQuery();
					connection.Close();
				}
			}
			return rowsAffected > 0;
		}

		public override Boolean Delete()
		{
			int rowsAffected = 0;
			using (SqlConnection connection = new SqlConnection(ConnectionString))
			{
				connection.Open();
				using (SqlCommand command = new SqlCommand("DELETE FROM POSplus_SignsPrinting " +
					"WHERE Upc = @Upc AND WeekEnding = @WeekEnding", connection))
				{

					command.Parameters.AddWithValue("@Upc", Upc);
					command.Parameters.AddWithValue("@WeekEnding", WeekEnding);
					rowsAffected = command.ExecuteNonQuery();
					connection.Close();
					Upc = "";
					WeekEnding = new DateTime(1900, 1, 1);
				}
				connection.Close();
			}
			return rowsAffected > 0;
		}

		public override string ToString()
		{
			StringBuilder builder = new StringBuilder();
			builder.Append(Desc1 + ",");
			builder.Append(Desc2 + ",");
			builder.Append(Desc3 + ",");
			builder.Append(Desc4 + ",");
			builder.Append(Desc5 + ",");
			builder.Append(FormatMoney(NormalPrice) + ",");
			builder.Append(PriceUom + ",");
			builder.Append(Selling + ",");
			builder.Append(Size + ",");
			builder.Append(Uom + ",");
			builder.Append(WeekEnding.ToString("MM/dd/yyyy") + ",");
			builder.Append(FormatMoney(YouSave));
			return builder.ToString();
		}
		#endregion

		public static List<Sign> GetAllNew(String connectionString, bool isNew)
		{
			List<Sign> result = new List<Sign>();
			using (SqlConnection connection = new SqlConnection(connectionString))
			{
				connection.Open();
				using (SqlCommand command = new SqlCommand("SELECT * FROM POSplus_SignsPrinting WHERE IsNew = @IsNew ORDER BY UPC", connection))
				{
					command.Parameters.AddWithValue("@IsNew", isNew);
					using (SqlDataReader reader = command.ExecuteReader())
					{
						while (reader.Read())
						{
							Sign sign = new Sign(connectionString);
							sign.Desc1 = DatabaseObject.FromDB<String>(reader["Desc1"]);
							sign.Desc2 = DatabaseObject.FromDB<String>(reader["Desc2"]);
							sign.Desc3 = DatabaseObject.FromDB<String>(reader["Desc3"]);
							sign.Desc4 = DatabaseObject.FromDB<String>(reader["Desc4"]);
							sign.Desc5 = DatabaseObject.FromDB<String>(reader["Desc5"]);
							sign.IsNew = DatabaseObject.FromDB<Boolean>(reader["IsNew"]);
							sign.NormalPrice = DatabaseObject.FromDB<Double>(reader["NormalPrice"]);
							sign.PriceUom = DatabaseObject.FromDB<Double>(reader["PriceUom"]);
							sign.Selling = DatabaseObject.FromDB<String>(reader["Selling"]);
							sign.Size = DatabaseObject.FromDB<String>(reader["Size"]);
							sign.Uom = DatabaseObject.FromDB<String>(reader["Uom"]);
							sign.Upc = DatabaseObject.FromDB<String>(reader["Upc"]);
							sign.WeekEnding = DatabaseObject.FromDB<DateTime>(reader["WeekEnding"]);
							sign.YouSave = DatabaseObject.FromDB<Double>(reader["YouSave"]);
							result.Add(sign);
						}
						reader.Close();
					}
				}
				connection.Close();
			}
			return result;
		}

		public static List<Sign> GetAll(string connectionString)
		{
			List<Sign> result = new List<Sign>();
			using (SqlConnection connection = new SqlConnection(connectionString))
			{
				connection.Open();
				using (SqlCommand command = new SqlCommand("SELECT * FROM POSplus_SignsPrinting", connection))
				{
					using (SqlDataReader reader = command.ExecuteReader())
					{
						while (reader.Read())
						{
							Sign sign = new Sign(connectionString);
							sign.Desc1 = DatabaseObject.FromDB<String>(reader["Desc1"]);
							sign.Desc2 = DatabaseObject.FromDB<String>(reader["Desc2"]);
							sign.Desc3 = DatabaseObject.FromDB<String>(reader["Desc3"]);
							sign.Desc4 = DatabaseObject.FromDB<String>(reader["Desc4"]);
							sign.Desc5 = DatabaseObject.FromDB<String>(reader["Desc5"]);
							sign.IsNew = DatabaseObject.FromDB<Boolean>(reader["IsNew"]);
							sign.NormalPrice = DatabaseObject.FromDB<Double>(reader["NormalPrice"]);
							sign.PriceUom = DatabaseObject.FromDB<Double>(reader["PriceUom"]);
							sign.Selling = DatabaseObject.FromDB<String>(reader["Selling"]);
							sign.Size = DatabaseObject.FromDB<String>(reader["Size"]);
							sign.Uom = DatabaseObject.FromDB<String>(reader["Uom"]);
							sign.Upc = DatabaseObject.FromDB<String>(reader["Upc"]);
							sign.WeekEnding = DatabaseObject.FromDB<DateTime>(reader["WeekEnding"]);
							sign.YouSave = DatabaseObject.FromDB<Double>(reader["YouSave"]);
							result.Add(sign);
						}
						reader.Close();
					}
					connection.Close();
				}
			}
			return result;
		}

		public static void TruncateTable(string LocalConnectionString)
		{
			using (SqlConnection connection = new SqlConnection(LocalConnectionString))
			{
				connection.Open();
				using (SqlCommand command = new SqlCommand("TRUNCATE TABLE POSplus_SignsPrinting", connection))
				{
					command.ExecuteNonQuery();
				}
				connection.Close();
			}
		}
    }
}